package com.donglu.handCardUsage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DataService {
	private DatabaseConfig cfg;
	public DataService(DatabaseConfig cfg) {
		this.cfg = cfg;
		
	}
	public List<Device> findAllDevice(){
		List<Device> list=new ArrayList<Device>();
		try(Connection conn = DatabaseUtil.getConnection(cfg.getIp(), cfg.getPort(), cfg.getDatabaseName(), cfg.getUserName(), cfg.getPassword(), DatabaseUtil.SQLSERVER2008);) {
			Statement statement = conn.createStatement();
			String sql = "select d.id,d.Identifier,d.Name,d.Address, l.address,d.Type,dg.code,dg.name from Device d left join Link l on d.Link=l.id left join Device2Group d2g on d.id=d2g.DeviceID left join DeviceGroup dg on dg.id=d2g.DeviceGroupID";
			ResultSet rs = statement.executeQuery(sql);
	        while (rs.next()) {
	        	Device device = new Device();
	            device.setId(rs.getBigDecimal(1).longValue());
	            device.setIdentifier(rs.getString(2));
	            device.setName(rs.getString(3));
	            device.setAddress(rs.getString(4));
	            device.setLinkAddress(rs.getString(5));
	            device.setType(rs.getString(6));
	            device.setGroupCode(rs.getString(7));
	            device.setGroupName(rs.getString(8));
//	            System.out.println(device);
	            list.add(device);
	        }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List<PhysicalCard> findAllCard(){
		List<PhysicalCard> list=new ArrayList<>();
		String sql="select p.id,p.SerialNumber,p.PhysicalId,u.identifier,u.name,u.groupCodeNameJoinStr,u.groupCodeJoinStr,u.IDCard,u.tel from PhysicalCard p left join CardUser u on p.cardUser=u.id where u.id is not null";
		try(Connection conn = DatabaseUtil.getConnection(cfg.getIp(), cfg.getPort(), cfg.getDatabaseName(), cfg.getUserName(), cfg.getPassword(), DatabaseUtil.SQLSERVER2008);) {
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sql);
	        while (rs.next()) {
	        	PhysicalCard p = new PhysicalCard();
	            p.setId(rs.getBigDecimal(1).longValue());
	            p.setIdentifier(rs.getString(2));
	            p.setSerilNumber(rs.getString(3));
	            p.setUserIdentifier(rs.getString(4));
	            p.setUserName(rs.getString(5));
	            p.setUserGroup(rs.getString(6));
	            p.setUserGroupCode(rs.getString(7));
	            p.setUserIDCard(rs.getString(8));
	            p.setUserPhone(rs.getString(9));
//	            System.out.println(p);
	            list.add(p);
	        }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	public boolean addCardUsage(Device device, PhysicalCard card, Date eventTime, Date timestamp) {
		String sql="insert into cardusage([type],[cardid],[cardSerialNumber],[deviceGroupCodeJoinStr],"
				+ "[deviceGroupCodeNameJoinStr],[deviceIdentifier],[deviceName],[deviceTypeEnum],[doorOpen],[doorSwitch],"
				+ "[emergency],[eventType],[preventBreak],[eventtime],[timestamp],[userGroupCodeJoinStr],"
				+ "[userGroupCodeNameJoinStr],[userIdentifier],[userName],[IDCard],[telPhone]) values"
				+ "('normal',?,?,?,?,?,?,?,"
				+ "1,1,1,0,1,?,?,?,?,?,?,?,?);";
		try(Connection conn = DatabaseUtil.getConnection(cfg.getIp(), cfg.getPort(), cfg.getDatabaseName(), cfg.getUserName(), cfg.getPassword(), DatabaseUtil.SQLSERVER2008);) {
			String checkSql="select COUNT(id) from CardUsage where cardid=? and eventtime=?";
			PreparedStatement checkPs = conn.prepareStatement(checkSql);
			checkPs.setString(1, card.getSerilNumber());
			checkPs.setTimestamp(2, new Timestamp(eventTime.getTime()));
			
			ResultSet rs = checkPs.executeQuery();
			if (rs.next()) {
				System.out.println(rs.getString(1));
				long l = rs.getLong(1);
				if (l>0) {
					return true;
				}
			}
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, card.getSerilNumber());
			ps.setString(2, card.getIdentifier());
			ps.setString(3, device.getGroupCode()==null?null:"["+device.getGroupCode()+"]");
			ps.setString(4, device.getGroupCode()==null?null:"("+device.getGroupCode()+")"+device.getGroupName());
			ps.setString(5, device.getIdentifier());
			ps.setString(6, device.getName());
			ps.setString(7, device.getType());
			ps.setTimestamp(8, new Timestamp(eventTime.getTime()));
			ps.setTimestamp(9, new Timestamp(timestamp.getTime()));
			ps.setString(10, card.getUserGroupCode());
			ps.setString(11, card.getUserGroup());
			ps.setString(12, card.getUserIdentifier());
			ps.setString(13, card.getUserName());
			ps.setString(14, card.getUserIDCard());
			ps.setString(15, card.getUserPhone());
			return ps.executeUpdate()>0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
}
